package operation.modules.operation.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import operation.config.MybatisPlusConfig;
import operation.constant.AppConstant;
import operation.constant.AppErrorCode;
import operation.constant.FileIOConstant;
import operation.exception.AppException;
import operation.modules.operation.dao.StatisticDao;
import operation.modules.operation.dto.ExportFieldDTO;
import operation.modules.operation.entity.StatisticEntity;
import operation.modules.operation.service.StatisticService;
import operation.util.ExcelUtil;
import operation.util.PageUtil;
import operation.util.ParamUtil;
import operation.util.Query;
import operation.util.ReqResult;
import operation.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.lang.NonNull;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

@Service("statisticService")
public class StatisticServiceImpl extends ServiceImpl<StatisticDao, StatisticEntity> implements StatisticService {
    private final Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private MybatisPlusConfig mybatisPlusConfig;

    /**
     * 分页查询
     *
     * @param params
     * @return
     */
    @Override
    public PageUtil queryPage(Map<String, Object> params) {
        // 分页
        IPage<StatisticEntity> page = new Query<StatisticEntity>().getPage(params);
        // 获取前台筛选参数
        String userName = (String) params.get("userName");
        String projectNumber = (String) params.get("projectNumber");
//        String dailyTime = (String) params.get("dailyTime");
        String startTime = (String) params.get("startTime");
        String endTime = (String) params.get("endTime");
        // sql 条件查询
        QueryWrapper<StatisticEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotEmpty(userName), "user_name", userName);
        queryWrapper.eq(StringUtils.isNotEmpty(projectNumber), "project_number", projectNumber);
//        queryWrapper.eq(StringUtils.isNotEmpty(dailyTime), "daily_time", dailyTime);
        queryWrapper.ge(StringUtils.isNotEmpty(startTime), "daily_time", startTime);
        queryWrapper.le(StringUtils.isNotEmpty(endTime), "daily_time", endTime);
        queryWrapper.eq("delete_flag", mybatisPlusConfig.getLogicNotDeleteValue());
        // 获取工时统计列表
        List<StatisticEntity> list = baseMapper.queryPage(page, queryWrapper, mybatisPlusConfig.getLogicNotDeleteValue());
        page.setRecords(list);
        return new PageUtil(page);
    }

    /**
     * 修改信息
     *
     * @param statisticEntity
     * @return
     */
    @Override
    public void updateStatistic(StatisticEntity statisticEntity) {
        statisticEntity.setUpdateTime(new Date());
        // 更新用户信息
        baseMapper.updateById(statisticEntity);
    }

    /**
     * 删除信息
     *
     * @param userIdList
     * @return
     */
    @Override
    public void deleteByIds(List<Long> userIdList) {
        // 创建Map对象
        Map<String, Object> map = new HashMap<>();
        map.put("userIdList", userIdList);
        map.put("updateTime", new Date());
        map.put("logicDeleteValue", mybatisPlusConfig.getLogicDeleteValue());
        // 删除用户信息
        baseMapper.deleteBatchUserIds(map);
    }

    /**
     * 导出
     */
    @Override
    public void export(HttpServletResponse response, Map<String, Object> params) {
        String id = (String) params.get("id");
        String sheetName = (String) params.get("sheetName");
        String filter = (String) params.get("filter");
        String fieldList = (String) params.get("fieldList");
        Set<Long> selectedIdSet = StringUtil.convertIds2Set(id);
        // 要导出的数据对象
        List<StatisticEntity> dataList;
        // 未选择要导出的属性值或者文件名不存在时抛出异常
        if (fieldList == null || StringUtils.isEmpty(fieldList) || StringUtils.isEmpty(sheetName)) {
            throw new AppException(AppErrorCode.MSG_PARAMETER_ERROR, AppErrorCode.PARAMETER_ERROR);
        }
        List<ExportFieldDTO> fieldDTOList = JSON.parseArray(fieldList, ExportFieldDTO.class);
        // 判断id集合是否为空
        if (CollectionUtil.isNotEmpty(selectedIdSet)) {
            dataList = queryExportByIds(selectedIdSet);
        } else {
            JSONObject filterList = JSON.parseObject(filter);
            dataList = queryExportByFilter(filterList);
        }
        // 要导出的数据对象集合是否为空
        if (CollectionUtil.isEmpty(dataList)) {
            throw new AppException(AppErrorCode.MSG_NO_DATA_TO_EXPORT, AppErrorCode.NO_DATA_TO_EXPORT);
        }
        ExcelUtil.export(sheetName, fieldDTOList, dataList, response);
    }

    /**
     * 根据id获取导出列表
     */
    private List<StatisticEntity> queryExportByIds(@NonNull Set<Long> selectedIds) {
        QueryWrapper<StatisticEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.in(CollectionUtils.isNotEmpty(selectedIds), "id", selectedIds);
        queryWrapper.eq("delete_flag", mybatisPlusConfig.getLogicNotDeleteValue());
        return baseMapper.queryExportList(queryWrapper, mybatisPlusConfig.getLogicNotDeleteValue());
    }

    /**
     * 根据查询条件获取导出列表
     */
    private List<StatisticEntity> queryExportByFilter(Map<String, Object> params) {
        // 筛选条件
        String userName = ParamUtil.getString(params, "userName");
        String projectNumber = ParamUtil.getString(params, "projectNumber");
        String startTime = (String) params.get("startTime");
        String endTime = (String) params.get("endTime");
        QueryWrapper<StatisticEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotEmpty(userName), "user_name", userName);
        queryWrapper.eq(StringUtils.isNotEmpty(projectNumber), "project_number", projectNumber);
        queryWrapper.ge(StringUtils.isNotEmpty(startTime), "daily_time", startTime);
        queryWrapper.le(StringUtils.isNotEmpty(endTime), "daily_time", endTime);
        queryWrapper.eq("delete_flag", mybatisPlusConfig.getLogicNotDeleteValue());
//        queryWrapper.orderByDesc("id");
        return baseMapper.queryExportList(queryWrapper, mybatisPlusConfig.getLogicNotDeleteValue());
    }

    /**
     * 导入数据
     * @param file
     * @param type
     */
    @Override
    @Transactional(value = AppConstant.DATABASE_TRANSACTION_MANAGER, rollbackFor = Exception.class)
    public ReqResult importData(MultipartFile file, String type) {
        // 从文件流获取工作簿对象
        Workbook workbook = getWorkBook(file);
        int sheetNumber = workbook.getNumberOfSheets(); // 获取工作表数量
        if (FileIOConstant.IMPORT_DATA_FILE_TYPE_DAILY.equals(type)) {
            saveStatistic(sheetNumber, workbook);
        } else {
            logger.error("[OneError]: FileService.importData: Import data type parameter error");
        }
        return ReqResult.ok();
    }

    /**
     * 保存工时统计数据
     *
     * @param sheetNumber 工作表数量
     * @param workbook    工作薄
     */
    private void saveStatistic(int sheetNumber, Workbook workbook) {
        StatisticEntity statisticEntity;
        List<StatisticEntity> statisticEntityList;
        List<StatisticEntity> result = new ArrayList<>();
        Sheet sheet;
        for (int sheetNum = 0; sheetNum < sheetNumber; sheetNum++) {
            statisticEntityList = new ArrayList<>();
            // 获取每个sheet
            sheet = workbook.getSheetAt(sheetNum);
            // 获取sheet的行数
            int rowCount = sheet.getLastRowNum();
            String userName = "";
            String dailyTime = "";
            //保证获取的行数正确
            if (rowCount - 8 >= 1) {
                // 遍历sheet
                for (int rowNum = 1; rowNum <= rowCount - 8; rowNum++) {
                    //创建数据对象
                    statisticEntity = new StatisticEntity();
                    // 获取row对象
                    Row row = sheet.getRow(rowNum);
                    // 获取名字和日期
                    if (row == null) {
                        throw new AppException(AppErrorCode.MSG_DB_DATA_NOT_EXIST, AppErrorCode.DB_DATA_NOT_EXIST);
                    } else {
                        if (rowNum == 1) {
                            // 检查cell数据 数据类型校验
                            checkString(row.getCell(2), 255, rowNum, 2, sheetNum);
                            checkString(row.getCell(4), 255, rowNum, 4, sheetNum);
                            // "："分割字符串
                            String[] timeStr = getCellValue(row.getCell(2)).trim().split("：");
                            if (timeStr.length <= 1) {
                                throw new AppException(AppErrorCode.MSG_IMPORT_FORMAT_ERROR + "," + (sheetNum + 1) + "," + (rowNum + 1), AppErrorCode.IMPORT_FORMAT_ERROR);
                            }
                            dailyTime = timeStr[1];
                            String[] nameStr = getCellValue(row.getCell(4)).trim().split("：");
                            if (nameStr.length <= 1) {
                                throw new AppException(AppErrorCode.MSG_IMPORT_FORMAT_ERROR + "," + (sheetNum + 1) + "," + (rowNum + 1), AppErrorCode.IMPORT_FORMAT_ERROR);
                            }
                            userName = nameStr[1];
                            rowNum += 2;
                            continue;
                        }
                    }

                    // 从第四行开始读取
                    // 检查cell数据 数据类型校验
                    checkString(row.getCell(2), 255, rowNum, 2, sheetNum);
                    checkString(row.getCell(3), 255, rowNum, 3, sheetNum);
                    checkString(row.getCell(4), 255, rowNum, 4, sheetNum);
                    checkNumber(row.getCell(5), 8, rowNum, 5, sheetNum);
                    checkString(row.getCell(7), 255, rowNum, 7, sheetNum);
//                    if (!"".equals(getCellValue(row.getCell(4)))) {
//                        checkComplete(row.getCell(6),11,rowNum,6,sheetNum);
//                    }

                    if (!"".equals(getCellValue(row.getCell(2)))) {
                        statisticEntity.setProjectNumber(getCellValue(row.getCell(2)));
                    }
                    if (!"".equals(getCellValue(row.getCell(3)))) {
                        statisticEntity.setProjectSubModule(getCellValue(row.getCell(3)));
                    }
                    if (!"".equals(getCellValue(row.getCell(4)))) {
                        statisticEntity.setJobContent(getCellValue(row.getCell(4)));
                    }
                    if (!"".equals(getCellValue(row.getCell(5)))) {
                        statisticEntity.setTimeSpent(new BigDecimal(getCellValue(row.getCell(5))));
                    }
                    if (!"".equals(getCellValue(row.getCell(7)))) {
                        statisticEntity.setRemark(getCellValue(row.getCell(7)));
                    }
                    // 将完成情况转换成数字
                    int completeStatus = -1;
                    if (!"".equals(getCellValue(row.getCell(4)))) {
                        completeStatus = 0;
                    }
                    if (AppConstant.COMPLETE.equals(String.valueOf(row.getCell(6)))) {
                        completeStatus = AppConstant.COMPLETE_STATE_COMPLETE;
                    } else if (AppConstant.PROCESSING.equals(String.valueOf(row.getCell(6)))) {
                        completeStatus = AppConstant.COMPLETE_STATE_PROCESSING;
                    } else if (AppConstant.UNFINISHED.equals(String.valueOf(row.getCell(6)))) {
                        completeStatus = AppConstant.COMPLETE_STATE_UNFINISHED;
                    }
                    if (completeStatus != -1) {
                        statisticEntity.setUserName(userName);
                        statisticEntity.setDailyTime(dailyTime);
                        statisticEntity.setCompleteStatus(completeStatus);
                        statisticEntity.setCreateTime(new Date());
                        statisticEntity.setUpdateTime(new Date());
                        statisticEntityList.add(statisticEntity);
                    }
                }
                result.addAll(statisticEntityList);
            } else {
                throw new AppException(AppErrorCode.MSG_PARAMETER_ERROR, AppErrorCode.PARAMETER_ERROR);
            }
        }
        if (CollectionUtils.isNotEmpty(result)) {
            baseMapper.batchInsert(result);
            baseMapper.deleteRepeat();
        }
    }

    /**
     * 获取工作簿
     *
     * @param file
     * @return
     */
    private Workbook getWorkBook(MultipartFile file) {
        // 获得文件名
        String fileName = file.getOriginalFilename();
        // 创建Workbook工作薄对象，包含整个excel
        Workbook workbook = null;
        try {
            // 获取excel文件的io流
            InputStream inputStream = file.getInputStream();
            // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName != null) {
                if (fileName.endsWith(FileIOConstant.FILE_TYPE_XLS)) {
                    workbook = new HSSFWorkbook(inputStream);
                } else if (fileName.endsWith(FileIOConstant.FILE_TYPE_XLSX)) {
                    workbook = new XSSFWorkbook(inputStream);
                }
            }
        } catch (IOException e) {
            logger.error("[OneError]: FileService.getWorkBook error, errorMessage: {}", e.getMessage(), e.fillInStackTrace());
        }
        return workbook;
    }

    /**
     * 检查字符格式
     * 将占位符的值拼接到最后，前台处理多语言
     * 半角逗号分隔，若需要使用逗号，请使用全角逗号
     * 第{0}行第{1}列数据过长,1,2
     *
     * @param cell
     * @param length
     * @param rowNum
     * @param colNum
     */
    private void checkString(Cell cell, int length, int rowNum, int colNum,int sheetNum) {
        if (cell == null || CellType.BLANK.equals(cell.getCellType())) {
            return;
        }
        if (getCellValue(cell).length() > length) {
            throw new AppException(AppErrorCode.MSG_CELL_LENGTH_ERROR + "," + (sheetNum + 1) + "," + (rowNum + 1) + "," + (colNum + 1), AppErrorCode.CELL_LENGTH_ERROR);
        }
    }

    /**
     * 检查数值格式
     * 将占位符的值拼接到最后，前台处理多语言
     * 半角逗号分隔，若需要使用逗号，请使用全角逗号
     * 第{0}行第{1}列数据类型错误,1,2
     * 第{0}行第{1}列数据过长,1,2
     *
     * @param cell
     * @param length
     * @param rowNum
     * @param colNum
     */
    private void checkNumber(Cell cell, int length, int rowNum, int colNum, int sheetNum) {
        if (cell == null || CellType.BLANK.equals(cell.getCellType())) {
            return;
        }
        if (!CellType.NUMERIC.equals(cell.getCellType())) {
            throw new AppException(AppErrorCode.MSG_CELL_TYPE_ERROR + "," + (sheetNum + 1) + "," + (rowNum + 1) + "," + (colNum + 1), AppErrorCode.CELL_TYPE_ERROR);
        }
        long cellValue = (long) cell.getNumericCellValue();
        if (Long.toString(cellValue).length() > length) {
            throw new AppException(AppErrorCode.MSG_CELL_LENGTH_ERROR + "," + (sheetNum + 1) + "," + (rowNum + 1) + "," + (colNum + 1), AppErrorCode.CELL_LENGTH_ERROR);
        }
    }

    /**
     * 检查状态
     * @param cell
     * @param length
     * @param rowNum
     * @param colNum
     * @param sheetNum
     */
    private void checkComplete(Cell cell, int length, int rowNum, int colNum, int sheetNum) {
        if (cell == null || CellType.BLANK.equals(cell.getCellType())) {
            throw new AppException(AppErrorCode.MSG_COMPLRETE_ERROR + "," + (sheetNum + 1) + "," + (rowNum + 1) + "," + (colNum + 1), AppErrorCode.COMPLRETE_ERROR);
        }
    }
    /**
     * 获取单元格的值
     * 解决当单元格里的内容是纯数字时使用row.getCell(0).getStringCellValue()报错
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String cellValue;
        if (cell == null) {
            cellValue = "";
            return cellValue;
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA:
                cellValue = cell.getCellFormula() + "";
                break;
            case BLANK:
                cellValue = "";
                break;
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
